﻿namespace Chain.DBUtility
{
    using SQLDMO;
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;
    using System.Text;
    using System.Text.RegularExpressions;

    public abstract class DbHelperSQL
    {
        public static string connectionString = PubConstant.ConnectionString;

        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandTimeout = 360;
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        public static bool ColumnExists(string tableName, string columnName)
        {
            object single = GetSingle("select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'");
            if (single == null)
            {
                return false;
            }
            return (Convert.ToInt32(single) > 0);
        }

        public static bool DataBaseBackup(string fullName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string cmdText = "use master;backup database [" + connection.Database + "] to disk='" + fullName + "'";
                connection.Open();
                SqlCommand command = new SqlCommand(cmdText, connection);
                command.ExecuteNonQuery();
                connection.Close();
                command.Dispose();
                return true;
            }
        }

        public static bool DataBaseRecover(string fullName)
        {
            string sQLString = string.Format("SELECT physical_name,name,file_id FROM master.sys.master_files WHERE database_id=DB_ID('{0}');", new SqlConnection(connectionString).Database.Trim());
            object[] args = new object[6];
            args[0] = new SqlConnection(connectionString).Database.Trim();
            args[1] = fullName;
            bool flag2 = false;
            using (DataTable table = Query(sQLString).Tables[0])
            {
                if (table.Rows.Count == 2)
                {
                    args[2] = table.Rows[0]["name"];
                    args[3] = table.Rows[0]["physical_name"];
                    args[4] = table.Rows[1]["name"];
                    args[5] = table.Rows[1]["physical_name"];
                    flag2 = true;
                }
                else
                {
                    using (DataTable table2 = Query(string.Format("RESTORE FILELISTONLY FROM DISK='{0}';", fullName)).Tables[0])
                    {
                        if (table2.Rows.Count == 2)
                        {
                            args[2] = table2.Rows[0]["LogicalName"];
                            args[3] = table2.Rows[0]["PhysicalName"];
                            args[4] = table2.Rows[1]["LogicalName"];
                            args[5] = table2.Rows[1]["PhysicalName"];
                            flag2 = true;
                        }
                    }
                }
            }
            if (flag2)
            {
                ExecuteSql(string.Format("RESTORE DATABASE {0} FROM DISK = '{1}' WITH MOVE '{2}' TO '{3}',MOVE '{4}' TO '{5}',STATS = 10, REPLACE;", args));
            }
            return flag2;
        }

        public static DataSet ExecuteProcedure(string procedure, SqlParameter[] parameter)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                DataSet dataSet = new DataSet();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = procedure;
                    if (parameter != null)
                    {
                        command.Parameters.AddRange(PrepareCommand(parameter));
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        adapter.Fill(dataSet);
                    }
                    command.Parameters.Clear();
                    connection.Close();
                }
                return dataSet;
            }
        }

        public static DataSet ExecuteProcedure(string procedure, SqlParameter[] parameter, string conString)
        {
            using (SqlConnection connection = new SqlConnection(conString))
            {
                connection.Open();
                DataSet dataSet = new DataSet();
                using (SqlCommand command = new SqlCommand())
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        command.Connection = connection;
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = procedure;
                        if (parameter != null)
                        {
                            command.Parameters.AddRange(PrepareCommand(parameter));
                        }
                        adapter.Fill(dataSet);
                        command.Parameters.Clear();
                        connection.Close();
                    }
                }
                return dataSet;
            }
        }

        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlDataReader reader2;
            strSQL = ProcessSql(strSQL);
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                reader2 = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException exception)
            {
                throw exception;
            }
            return reader2;
        }

        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlDataReader reader2;
            SQLString = ProcessSql(SQLString);
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, conn, null, SQLString, cmdParms);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                reader2 = reader;
            }
            catch (SqlException exception)
            {
                throw exception;
            }
            return reader2;
        }

        public static int ExecuteSql(string SQLString)
        {
            int num2;
            SQLString = ProcessSql(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        num2 = command.ExecuteNonQuery();
                    }
                    catch (SqlException exception)
                    {
                        throw exception;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return num2;
        }

        public static int ExecuteSql(string SQLString, string content)
        {
            int num2;
            SQLString = ProcessSql(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(SQLString, connection);
                SqlParameter parameter = new SqlParameter("@content", SqlDbType.NText);
                parameter.Value = content;
                command.Parameters.Add(parameter);
                try
                {
                    connection.Open();
                    num2 = command.ExecuteNonQuery();
                }
                catch (SqlException exception)
                {
                    throw exception;
                }
                finally
                {
                    command.Dispose();
                    connection.Close();
                }
            }
            return num2;
        }

        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            int num2;
            SQLString = ProcessSql(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(command, connection, null, SQLString, cmdParms);
                        int num = command.ExecuteNonQuery();
                        command.Parameters.Clear();
                        num2 = num;
                    }
                    catch (SqlException exception)
                    {
                        throw exception;
                    }
                    finally
                    {
                        connection.Close();
                        command.Dispose();
                    }
                }
            }
            return num2;
        }

        public static object ExecuteSqlGet(string SQLString, string content)
        {
            object obj3;
            SQLString = ProcessSql(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(SQLString, connection);
                SqlParameter parameter = new SqlParameter("@content", SqlDbType.NText);
                parameter.Value = content;
                command.Parameters.Add(parameter);
                try
                {
                    connection.Open();
                    object objA = command.ExecuteScalar();
                    if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
                    {
                        return null;
                    }
                    obj3 = objA;
                }
                catch (SqlException exception)
                {
                    throw exception;
                }
                finally
                {
                    command.Dispose();
                    connection.Close();
                }
            }
            return obj3;
        }

        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            int num2;
            strSQL = ProcessSql(strSQL);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(strSQL, connection);
                SqlParameter parameter = new SqlParameter("@fs", SqlDbType.Image);
                parameter.Value = fs;
                command.Parameters.Add(parameter);
                try
                {
                    connection.Open();
                    num2 = command.ExecuteNonQuery();
                }
                catch (SqlException exception)
                {
                    throw exception;
                }
                finally
                {
                    command.Dispose();
                    connection.Close();
                }
            }
            return num2;
        }

        public static int ExecuteSqlTran(List<CommandInfo> cmdList)
        {
            int num3;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        int num = 0;
                        foreach (CommandInfo info in cmdList)
                        {
                            string cmdText = ProcessSql(info.CommandText);
                            SqlParameter[] parameters = (SqlParameter[]) info.Parameters;
                            PrepareCommand(cmd, connection, transaction, cmdText, parameters);
                            if ((info.EffentNextType == EffentNextType.WhenHaveContine) || (info.EffentNextType == EffentNextType.WhenNoHaveContine))
                            {
                                if (info.CommandText.ToLower().IndexOf("count(") == -1)
                                {
                                    transaction.Rollback();
                                    return 0;
                                }
                                object obj2 = cmd.ExecuteScalar();
                                bool flag = false;
                                if ((obj2 == null) && (obj2 == DBNull.Value))
                                {
                                    flag = false;
                                }
                                flag = Convert.ToInt32(obj2) > 0;
                                if ((info.EffentNextType == EffentNextType.WhenHaveContine) && !flag)
                                {
                                    transaction.Rollback();
                                    return 0;
                                }
                                if ((info.EffentNextType == EffentNextType.WhenNoHaveContine) && flag)
                                {
                                    transaction.Rollback();
                                    return 0;
                                }
                                continue;
                            }
                            int num2 = cmd.ExecuteNonQuery();
                            num += num2;
                            if ((info.EffentNextType == EffentNextType.ExcuteEffectRows) && (num2 == 0))
                            {
                                transaction.Rollback();
                                return 0;
                            }
                            cmd.Parameters.Clear();
                        }
                        transaction.Commit();
                        num3 = num;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        transaction.Dispose();
                    }
                }
            }
            return num3;
        }

        public static bool ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                SqlTransaction transaction = connection.BeginTransaction();
                command.Transaction = transaction;
                string str = "";
                try
                {
                    for (int i = 0; i < SQLStringList.Count; i++)
                    {
                        str = ProcessSql((string) SQLStringList[i]);
                        if (str.Trim().Length > 1)
                        {
                            command.CommandText = str;
                            command.ExecuteNonQuery();
                        }
                    }
                    transaction.Commit();
                    return true;
                }
                catch
                {
                    transaction.Rollback();
                    return false;
                }
                finally
                {
                    connection.Close();
                    transaction.Dispose();
                }
            }
            return false;
        }

        public static bool ExecuteSqlTranPara(ArrayList SQLStringList)
        {
            bool flag = false;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        try
                        {
                            foreach (DictionaryEntry entry in SQLStringList)
                            {
                                string cmdText = ProcessSql(entry.Key.ToString());
                                SqlParameter[] cmdParms = (SqlParameter[]) entry.Value;
                                PrepareCommand(cmd, connection, transaction, cmdText, cmdParms);
                                cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                            }
                            transaction.Commit();
                            flag = true;
                        }
                        catch
                        {
                            transaction.Rollback();
                            flag = false;
                            throw;
                        }
                        return flag;
                    }
                    finally
                    {
                        connection.Close();
                        transaction.Dispose();
                        cmd.Dispose();
                    }
                    return flag;
                }
            }
        }

        public static void ExecuteSqlTranWithIndentity(List<CommandInfo> SQLStringList)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        int num = 0;
                        foreach (CommandInfo info in SQLStringList)
                        {
                            string cmdText = ProcessSql(info.CommandText);
                            SqlParameter[] parameters = (SqlParameter[]) info.Parameters;
                            foreach (SqlParameter parameter in parameters)
                            {
                                if (parameter.Direction == ParameterDirection.InputOutput)
                                {
                                    parameter.Value = num;
                                }
                            }
                            PrepareCommand(cmd, connection, transaction, cmdText, parameters);
                            cmd.ExecuteNonQuery();
                            foreach (SqlParameter parameter2 in parameters)
                            {
                                if (parameter2.Direction == ParameterDirection.Output)
                                {
                                    num = Convert.ToInt32(parameter2.Value);
                                }
                            }
                            cmd.Parameters.Clear();
                        }
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        transaction.Dispose();
                    }
                }
            }
        }

        public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        int num = 0;
                        foreach (DictionaryEntry entry in SQLStringList)
                        {
                            string cmdText = ProcessSql(entry.Key.ToString());
                            SqlParameter[] cmdParms = (SqlParameter[]) entry.Value;
                            foreach (SqlParameter parameter in cmdParms)
                            {
                                if (parameter.Direction == ParameterDirection.InputOutput)
                                {
                                    parameter.Value = num;
                                }
                            }
                            PrepareCommand(cmd, connection, transaction, cmdText, cmdParms);
                            cmd.ExecuteNonQuery();
                            foreach (SqlParameter parameter2 in cmdParms)
                            {
                                if (parameter2.Direction == ParameterDirection.Output)
                                {
                                    num = Convert.ToInt32(parameter2.Value);
                                }
                            }
                            cmd.Parameters.Clear();
                        }
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                        cmd.Dispose();
                        transaction.Dispose();
                    }
                }
            }
        }

        public static bool Exists(string strSql)
        {
            int num;
            strSql = ProcessSql(strSql);
            object single = GetSingle(strSql);
            if (object.Equals(single, null) || object.Equals(single, DBNull.Value))
            {
                num = 0;
            }
            else
            {
                num = int.Parse(single.ToString());
            }
            if (num == 0)
            {
                return false;
            }
            return true;
        }

        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            int num;
            strSql = ProcessSql(strSql);
            object single = GetSingle(strSql, cmdParms);
            if (object.Equals(single, null) || object.Equals(single, DBNull.Value))
            {
                num = 0;
            }
            else
            {
                num = int.Parse(single.ToString());
            }
            if (num == 0)
            {
                return false;
            }
            return true;
        }

        private static string getColumns(string[] column)
        {
            StringBuilder builder = new StringBuilder();
            for (int i = 0; i < column.Length; i++)
            {
                builder.AppendFormat("{0},", Regex.Replace(column[i], @"\s{2,}", " ").Trim(new char[] { ',' }));
            }
            return Regex.Replace(builder.ToString(), @"\s{0,},\s{0,}", ",").Trim(new char[] { ',' });
        }

        private static string getCondition(string[] condition)
        {
            StringBuilder builder = new StringBuilder("1=1");
            if ((condition != null) && (condition.Length > 0))
            {
                for (int i = 0; i < condition.Length; i++)
                {
                    if (!(condition[i] == ""))
                    {
                        builder.AppendFormat(" AND {0}", condition[i]);
                    }
                }
            }
            return ProcessSql(Regex.Replace(builder.ToString(), @"\s{2,}", " ").Trim());
        }

        public static string GetDataBaseName()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string database = connection.Database;
                connection.Close();
                return database;
            }
        }

        private static string getinnerTableName(List<string> _innerjoinlist)
        {
            StringBuilder builder = new StringBuilder();
            for (int i = 0; i < _innerjoinlist.Count; i++)
            {
                builder.AppendFormat("{0} ", Regex.Replace(_innerjoinlist[i], @"\s{2,}", " "));
            }
            return builder.ToString().Trim();
        }

        public static int GetMaxID(string FieldName, string TableName)
        {
            object single = GetSingle("select max(" + FieldName + ")+1 from " + TableName);
            if (single == null)
            {
                return 1;
            }
            return int.Parse(single.ToString());
        }

        public static object GetSingle(string SQLString)
        {
            object obj3;
            SQLString = ProcessSql(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object objA = command.ExecuteScalar();
                        if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
                        {
                            return null;
                        }
                        obj3 = objA;
                    }
                    catch (SqlException exception)
                    {
                        throw exception;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return obj3;
        }

        public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
        {
            object obj3;
            SQLString = ProcessSql(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(command, connection, null, SQLString, cmdParms);
                        object objA = command.ExecuteScalar();
                        command.Parameters.Clear();
                        if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
                        {
                            return null;
                        }
                        obj3 = objA;
                    }
                    catch (SqlException exception)
                    {
                        throw exception;
                    }
                    finally
                    {
                        connection.Close();
                        command.Dispose();
                    }
                }
            }
            return obj3;
        }

        public static object GetSingle(string SQLString, int Times)
        {
            object obj3;
            SQLString = ProcessSql(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        command.CommandTimeout = Times;
                        object objA = command.ExecuteScalar();
                        if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
                        {
                            return null;
                        }
                        obj3 = objA;
                    }
                    catch (SqlException exception)
                    {
                        throw exception;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return obj3;
        }

        public static DataSet GetTable(string[] column, string[] condition, string IndexColumn, bool IsAsc, int PageSize, int Page, string procName)
        {
            string str = getColumns(column);
            string str2 = getCondition(condition);
            List<SqlParameter> list = new List<SqlParameter>();
            SqlParameter item = new SqlParameter();
            item.ParameterName = "@ReturnFields";
            item.Value = str;
            item.SqlDbType = SqlDbType.VarChar;
            item.Size = 500;
            list.Add(item);
            SqlParameter parameter2 = new SqlParameter();
            parameter2.ParameterName = "@OrderFields";
            parameter2.Value = IndexColumn;
            parameter2.SqlDbType = SqlDbType.VarChar;
            parameter2.Size = 0xff;
            list.Add(parameter2);
            SqlParameter parameter3 = new SqlParameter();
            parameter3.ParameterName = "@PKField";
            parameter3.Value = IndexColumn;
            parameter3.SqlDbType = SqlDbType.VarChar;
            parameter3.Size = 0xff;
            list.Add(parameter3);
            SqlParameter parameter4 = new SqlParameter();
            parameter4.ParameterName = "@IsDesc";
            parameter4.Value = IsAsc ? 0 : 1;
            parameter4.SqlDbType = SqlDbType.Bit;
            list.Add(parameter4);
            SqlParameter parameter5 = new SqlParameter();
            parameter5.ParameterName = "@Where";
            parameter5.Value = str2;
            parameter5.SqlDbType = SqlDbType.VarChar;
            parameter5.Size = 0x3e8;
            list.Add(parameter5);
            SqlParameter parameter6 = new SqlParameter();
            parameter6.ParameterName = "@PageSize";
            parameter6.Value = PageSize;
            parameter6.SqlDbType = SqlDbType.Int;
            list.Add(parameter6);
            SqlParameter parameter7 = new SqlParameter();
            parameter7.ParameterName = "@PageIndex";
            parameter7.Value = Page;
            parameter7.SqlDbType = SqlDbType.Int;
            list.Add(parameter7);
            return ExecuteProcedure(procName, list.ToArray());
        }

        public static DataSet GetTable(string TableName, string[] column, string[] condition, string IndexColumn, bool IsAsc, int PageSize, int Page, int RecordCount)
        {
            string str = getColumns(column);
            string str2 = getCondition(condition);
            List<SqlParameter> list = new List<SqlParameter>();
            SqlParameter item = new SqlParameter();
            item.ParameterName = "@TableName";
            item.Value = TableName;
            item.SqlDbType = SqlDbType.NVarChar;
            item.Size = 500;
            list.Add(item);
            SqlParameter parameter2 = new SqlParameter();
            parameter2.ParameterName = "@ReturnFields";
            parameter2.Value = str;
            parameter2.SqlDbType = SqlDbType.NVarChar;
            parameter2.Size = 0xfa0;
            list.Add(parameter2);
            SqlParameter parameter3 = new SqlParameter();
            parameter3.ParameterName = "@OrderFields";
            parameter3.Value = IndexColumn;
            parameter3.SqlDbType = SqlDbType.NVarChar;
            parameter3.Size = 500;
            list.Add(parameter3);
            SqlParameter parameter4 = new SqlParameter();
            parameter4.ParameterName = "@PageSize";
            parameter4.Value = PageSize;
            parameter4.SqlDbType = SqlDbType.Int;
            parameter4.Size = 4;
            list.Add(parameter4);
            SqlParameter parameter5 = new SqlParameter();
            parameter5.ParameterName = "@PageIndex";
            parameter5.Value = Page;
            parameter5.SqlDbType = SqlDbType.Int;
            parameter5.Size = 4;
            list.Add(parameter5);
            SqlParameter parameter6 = new SqlParameter();
            parameter6.ParameterName = "@PKField";
            parameter6.Value = IndexColumn;
            parameter6.SqlDbType = SqlDbType.NVarChar;
            parameter6.Size = 0xff;
            list.Add(parameter6);
            SqlParameter parameter7 = new SqlParameter();
            parameter7.ParameterName = "@IsDesc";
            parameter7.Value = IsAsc ? 0 : 1;
            parameter7.SqlDbType = SqlDbType.Bit;
            list.Add(parameter7);
            SqlParameter parameter8 = new SqlParameter();
            parameter8.ParameterName = "@Where";
            parameter8.Value = str2;
            parameter8.SqlDbType = SqlDbType.NVarChar;
            parameter8.Size = 0xfa0;
            list.Add(parameter8);
            return ExecuteProcedure("CP_PageNew", list.ToArray());
        }

        public static DataSet GetTable(List<string> innerJointable, string[] column, string[] condition, string IndexColumn, string KeyField, bool IsAsc, int PageSize, int Page, int RecordCount)
        {
            string str = getColumns(column);
            string str2 = getCondition(condition);
            List<SqlParameter> list = new List<SqlParameter>();
            SqlParameter item = new SqlParameter();
            item.ParameterName = "@SqlTables";
            item.Value = getinnerTableName(innerJointable);
            item.SqlDbType = SqlDbType.NVarChar;
            item.Size = 500;
            list.Add(item);
            SqlParameter parameter2 = new SqlParameter();
            parameter2.ParameterName = "@SqlColumns";
            parameter2.Value = str;
            parameter2.SqlDbType = SqlDbType.NVarChar;
            parameter2.Size = 0xfa0;
            list.Add(parameter2);
            SqlParameter parameter3 = new SqlParameter();
            parameter3.ParameterName = "@OrderFields";
            parameter3.Value = IndexColumn;
            parameter3.SqlDbType = SqlDbType.NVarChar;
            parameter3.Size = 500;
            list.Add(parameter3);
            SqlParameter parameter4 = new SqlParameter();
            parameter4.ParameterName = "@PageSize";
            parameter4.Value = PageSize;
            parameter4.SqlDbType = SqlDbType.Int;
            parameter4.Size = 4;
            list.Add(parameter4);
            SqlParameter parameter5 = new SqlParameter();
            parameter5.ParameterName = "@PageIndex";
            parameter5.Value = Page;
            parameter5.SqlDbType = SqlDbType.Int;
            parameter5.Size = 4;
            list.Add(parameter5);
            SqlParameter parameter6 = new SqlParameter();
            parameter6.ParameterName = "@Where";
            parameter6.Value = str2;
            parameter6.SqlDbType = SqlDbType.NVarChar;
            parameter6.Size = 0xfa0;
            list.Add(parameter6);
            return ExecuteProcedure("SP_PageListCount", list.ToArray());
        }

        public static DataSet GetTable(string TableName, string[] column, string[] condition, string IndexColumn, string KeyField, bool IsAsc, int PageSize, int Page, int RecordCount)
        {
            string str = getColumns(column);
            string str2 = getCondition(condition);
            List<SqlParameter> list = new List<SqlParameter>();
            SqlParameter item = new SqlParameter();
            item.ParameterName = "@TableName";
            item.Value = TableName;
            item.SqlDbType = SqlDbType.NVarChar;
            item.Size = 500;
            list.Add(item);
            SqlParameter parameter2 = new SqlParameter();
            parameter2.ParameterName = "@ReturnFields";
            parameter2.Value = str;
            parameter2.SqlDbType = SqlDbType.NVarChar;
            parameter2.Size = 0xfa0;
            list.Add(parameter2);
            SqlParameter parameter3 = new SqlParameter();
            parameter3.ParameterName = "@OrderFields";
            parameter3.Value = IndexColumn;
            parameter3.SqlDbType = SqlDbType.NVarChar;
            parameter3.Size = 500;
            list.Add(parameter3);
            SqlParameter parameter4 = new SqlParameter();
            parameter4.ParameterName = "@PageSize";
            parameter4.Value = PageSize;
            parameter4.SqlDbType = SqlDbType.Int;
            parameter4.Size = 4;
            list.Add(parameter4);
            SqlParameter parameter5 = new SqlParameter();
            parameter5.ParameterName = "@PageIndex";
            parameter5.Value = Page;
            parameter5.SqlDbType = SqlDbType.Int;
            parameter5.Size = 4;
            list.Add(parameter5);
            SqlParameter parameter6 = new SqlParameter();
            parameter6.ParameterName = "@PKField";
            parameter6.Value = KeyField;
            parameter6.SqlDbType = SqlDbType.NVarChar;
            parameter6.Size = 0xff;
            list.Add(parameter6);
            SqlParameter parameter7 = new SqlParameter();
            parameter7.ParameterName = "@IsDesc";
            parameter7.Value = IsAsc ? 0 : 1;
            parameter7.SqlDbType = SqlDbType.Bit;
            list.Add(parameter7);
            SqlParameter parameter8 = new SqlParameter();
            parameter8.ParameterName = "@Where";
            parameter8.Value = str2;
            parameter8.SqlDbType = SqlDbType.NVarChar;
            parameter8.Size = 0xfa0;
            list.Add(parameter8);
            return ExecuteProcedure("CP_PageNew", list.ToArray());
        }

        private static SqlParameter[] PrepareCommand(SqlParameter[] cmdParms)
        {
            SqlParameter[] parameterArray = new SqlParameter[cmdParms.Length];
            int index = 0;
            foreach (SqlParameter parameter in cmdParms)
            {
                if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                parameterArray[index] = parameter;
                index++;
            }
            return parameterArray;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        public static string ProcessSql(string inputSql)
        {
            string pattern = @"#(\d{4}-\d{1,2}-\d{1,2}[^#]*)#";
            return Regex.Replace(inputSql, pattern, "'$1'");
        }

        public static DataSet Query(string SQLString)
        {
            SQLString = ProcessSql(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                try
                {
                    connection.Open();
                    new SqlDataAdapter(SQLString, connection).Fill(dataSet, "ds");
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                finally
                {
                    connection.Close();
                }
                return dataSet;
            }
        }

        public static DataSet Query(string SQLString, int Times)
        {
            SQLString = ProcessSql(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(SQLString, connection);
                    adapter.SelectCommand.CommandTimeout = Times;
                    adapter.Fill(dataSet, "ds");
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                finally
                {
                    connection.Close();
                }
                return dataSet;
            }
        }

        public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            DataSet set2;
            SQLString = ProcessSql(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    DataSet dataSet = new DataSet();
                    try
                    {
                        adapter.Fill(dataSet, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (SqlException exception)
                    {
                        throw new Exception(exception.Message);
                    }
                    finally
                    {
                        connection.Close();
                        adapter.Dispose();
                    }
                    set2 = dataSet;
                }
            }
            return set2;
        }

        public static bool RestoreDB(string fullName)
        {
            bool flag = false;
            SQLServerClass serverObject = new SQLServerClass();
            try
            {
                string serverName = Regex.Match(connectionString, "server=(.*?);").Groups[1].Value;
                string str2 = Regex.Match(connectionString, "database=(.*?);").Groups[1].Value;
                string login = Regex.Match(connectionString, "uid=(.*?);").Groups[1].Value;
                string password = Regex.Match(connectionString, "pwd=(.*?);").Groups[1].Value;
                serverObject.LoginSecure = false;
                serverObject.Connect(serverName, login, password);
                QueryResults results = serverObject.EnumProcesses(-1);
                int column = -1;
                int num2 = -1;
                for (int i = 1; i <= results.Columns; i++)
                {
                    string str5 = results.get_ColumnName(i);
                    if (str5.ToUpper().Trim() == "SPID")
                    {
                        column = i;
                    }
                    else if (str5.ToUpper().Trim() == "DBNAME")
                    {
                        num2 = i;
                    }
                    if ((column != -1) && (num2 != -1))
                    {
                        break;
                    }
                }
                for (int j = 1; j <= results.Rows; j++)
                {
                    int columnLong = results.GetColumnLong(j, column);
                    if (results.GetColumnString(j, num2).ToUpper() == str2.ToUpper())
                    {
                        serverObject.KillProcess(columnLong);
                    }
                }
                RestoreClass class3 = new RestoreClass();
                class3.Action = SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                class3.Database = str2;
                class3.Files = fullName;
                class3.FileNumber = 1;
                class3.ReplaceDatabase = true;
                class3.SQLRestore(serverObject);
                flag = true;
            }
            catch
            {
                flag = false;
            }
            finally
            {
                serverObject.DisConnect();
            }
            return flag;
        }

        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                adapter.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                return (int) command.Parameters["ReturnValue"].Value;
            }
        }

        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                adapter.SelectCommand.CommandTimeout = Times;
                adapter.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        public static bool TabExists(string TableName)
        {
            int num;
            object single = GetSingle("select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1");
            if (object.Equals(single, null) || object.Equals(single, DBNull.Value))
            {
                num = 0;
            }
            else
            {
                num = int.Parse(single.ToString());
            }
            if (num == 0)
            {
                return false;
            }
            return true;
        }
    }
}

